//
// Copyright (c) 2014, 保留所有权利。
//
// CstUtil.java
//
// 作    者：王红燕
// 当前版本：1.0
// 2014 下午5:36:13
//
// 
//
package com.vanke.agent.common;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import net.sf.json.JSONObject;

import org.apache.log4j.Logger;

import com.vanke.agent.ADataSource;
import com.vanke.agent.C;
import com.vanke.agent.bean.CstInfo;
import com.vanke.agent.common.log.LoggerUtil;
import com.vanke.agent.sync.AgentContext;

/**
 *
 */
public class CstUtil {
	static Logger log = Logger.getLogger(CstUtil.class);
	static Logger readLog = LoggerUtil.getLoggerByName("read_cst");

	static Set<String> cstTables = new HashSet<String>();
	static Set<String> memberTables = new HashSet<String>();
	static Set<String> mobileMapping = new HashSet<String>();
	static Set<String> cardMapping = new HashSet<String>();
	static Set<String> guidMapping = new HashSet<String>();
	static Set<String> productOrderMapping = new HashSet<String>();
	static Set<String> cstReceiveRecord = new HashSet<String>();
	static Set<String> cstSaleService = new HashSet<String>();

	static {
		loadSplitTables();
	} // ~static

	/**
	 * 加载已有的客户表和客户映射表的表名
	 */
	private static void loadSplitTables() {
		Connection con = ADataSource.getConnection(ADataSource.SCHEMA_BIZ);
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery("show tables");
			while (rs.next()) {
				String table = rs.getString(1);
				if (table.toLowerCase().startsWith("cst_customer_")) {
					cstTables.add(table);
				} else if (table.toLowerCase().startsWith(
						"cst_cstcardidmapping_")) {
					cardMapping.add(table);
				} else if (table.toLowerCase().startsWith(
						"cst_cstmobilemapping_")) {
					mobileMapping.add(table);
				} else if (table.toLowerCase()
						.startsWith("cst_cstguidmapping_")) {
					guidMapping.add(table);
				} else if (table.toLowerCase().startsWith("cst_product_order_")) {
					productOrderMapping.add(table);
				} else if (table.toLowerCase().startsWith("cst_followrecord_")) {
					cstReceiveRecord.add(table);
				} else if (table.toLowerCase().startsWith("cst_member_")) {
					memberTables.add(table);
				} else if (table.toLowerCase().startsWith("s_saleservice_")) {
					cstSaleService.add(table);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ADataSource.close(rs);
			ADataSource.close(stmt);
			ADataSource.close(con);
		}
	} // ~loadCustomerTables

	public static List<String> loadTables(Connection con, String prefix) {
		Statement stmt = null;
		ResultSet rs = null;
		List<String> tables = new ArrayList<String>(10);
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery("show tables");
			while (rs.next()) {
				String t = rs.getString(1);
				if (t.toLowerCase().startsWith(prefix)) {
					tables.add(t);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			AgentContext.exit("加载映射表缓存失败，无法继续同步；否则将会产生不可预知的垃圾数据");
		} finally {
			ADataSource.close(rs, stmt, null);
		}
		return tables;
	}

	/**
	 * 计算获取按客户GUID分表的表名计算；系统中所有涉及按客户GUID分表的表名都必须用此方法获取<br>
	 * 以实现分表规则的统一
	 * 
	 * @param prefix 表名前缀
	 * @param guid 客户GUID
	 * @return 根据客户GUID分表后的表名
	 */
	public static String calcCstSplitTable(String prefix, long guid) {
		return prefix + guid / 1000000;
	}

	public static String retrieveCustomerTable(long uid) {
		String table = calcCstSplitTable("cst_Customer_", uid);
		if (!cstTables.contains(table)) {
			if (createCustomerTable(table)) {
				cstTables.add(table);
			}
		}
		return table;
	} // ~

	public static String retrieveMemberTable(long uid) {
		String table = calcCstSplitTable("cst_Member_", uid);
		if (!memberTables.contains(table)) {
			if (createMemberTable(table)) {
				memberTables.add(table);
			}
		}
		return table;
	} // ~

	public static String retrieveSaleServiceTable(long uid) {
		String table = calcCstSplitTable("s_SaleService_", uid);
		if (!cstSaleService.contains(table)) {
			if (createSaleServiceTable(table)) {
				cstSaleService.add(table);
			}
		}
		return table;
	} // ~

	public static String retrieveCustomerOrderTable(long uid) {
		if (uid == 0) {
			return "cst_Product_Order";
		}
		String table = calcCstSplitTable("cst_Product_Order_", uid);
		if (!productOrderMapping.contains(table)) {
			if (createCustomerOrderTable(table)) {
				productOrderMapping.add(table);
			}
		}
		return table;
	} // ~

	public static String retrieveCstReceiveTable(long uid) {
		if (uid == 0) {
			return "cst_FollowRecord";
		}
		String table = calcCstSplitTable("cst_FollowRecord_", uid);
		if (!cstReceiveRecord.contains(table)) {
			if (createCstReceiveTable(table)) {
				cstReceiveRecord.add(table);
			}
		}
		return table;
	} // ~

	public static String retrieveGUIDMapping(String guid) {
		String table = "cst_CstGUIDMapping_"
				+ guid.toUpperCase().charAt(guid.length() - 1);
		if (!guidMapping.contains(table)) {
			if (createGUIDMappingTable(table)) {
				guidMapping.add(table);
			}
		}
		return table;
	} // ~

	public static String retrieveMobileMapping(String mobile) {
		if (mobile == null || mobile.length() < 11 || !Util.isMobile(mobile)) {
			return "cst_CstMobileMapping_notexists";
		}
		String table = "cst_CstMobileMapping_"
				+ mobile.substring(mobile.length() - 2);
		if (!mobileMapping.contains(table)) {
			if (createMobileMappingTable(table)) {
				mobileMapping.add(table);
			}
		}
		return table;
	} // ~String retrieveMobileMapping(String mobile)

	public static String retrieveCardMapping(String cardid) {
		if (cardid == null || (cardid.length() != 15 && cardid.length() != 18)
				|| !CardID.valid(cardid)) {
			return "cst_CstCardIdMapping";
		}
		String table = "cst_CstCardIdMapping_"
				+ cardid.toUpperCase().substring(cardid.length() - 2);
		if (!cardMapping.contains(table)) {
			if (createCardMappingTable(table)) {
				cardMapping.add(table);
			}
		}
		return table;
	} // ~retrieveCardMapping(String cardid)

	/**
	 * 创建客户分表
	 */
	static boolean createCustomerTable(String table) {
		String sql = "create table if not exists " + table
				+ "(CstGUID bigint(20) not null primary key) "
				+ "select * from cst_Customer where 1=2";
		Connection con = ADataSource.getConnection(ADataSource.SCHEMA_BIZ);
		Statement stmt = null;
		try {
			stmt = con.createStatement();

			log.info("execute " + sql);
			stmt.execute(sql);

			return true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ADataSource.close(stmt);
			ADataSource.close(con);
		}

		return true;
	} // ~createCustomerTable

	/**
	 * 创建客户订购关系分表
	 */
	static boolean createCustomerOrderTable(String table) {
		String sql = "create table if not exists "
				+ table
				+ "(POrderGUID bigint(20) not null primary key AUTO_INCREMENT) "
				+ "select * from cst_Product_Order where 1=2";
		Connection con = ADataSource.getConnection(ADataSource.SCHEMA_BIZ);
		Statement stmt = null;
		try {
			stmt = con.createStatement();

			log.info("execute " + sql);
			stmt.execute(sql);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ADataSource.close(stmt);
			ADataSource.close(con);
		}
		return true;
	} // ~createCustomerOrderTable

	/**
	 * 创建客户跟进记录分表
	 */
	static boolean createCstReceiveTable(String table) {
		String sql = "create table if not exists " + table
				+ "(PkIndex bigint(20) primary key AUTO_INCREMENT) "
				+ "select * from cst_FollowRecord where 1=2";
		Connection con = ADataSource.getConnection(ADataSource.SCHEMA_BIZ);
		Statement stmt = null;
		try {
			stmt = con.createStatement();

			log.info("execute " + sql);
			stmt.execute(sql);

			return true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ADataSource.close(stmt);
			ADataSource.close(con);
		}

		return true;
	} // ~createCustomerOrderTable

	/**
	 * 创建uid与手机的映射表
	 */
	static boolean createMobileMappingTable(String table) {
		String sql = "CREATE TABLE " + table + " ("
				+ "mobile varchar(32) primary key,"
				+ "CstGUID bigint(20) NOT NULL)";
		Connection con = ADataSource.getConnection(ADataSource.SCHEMA_BIZ);
		Statement stmt = null;
		try {
			stmt = con.createStatement();

			log.info("execute " + sql);
			stmt.execute(sql);

			return true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ADataSource.close(stmt);
			ADataSource.close(con);
		}

		return true;
	} // ~createMobileMappingTable

	/**
	 * 创建uid与身份证的映射表
	 */
	static boolean createCardMappingTable(String table) {
		String sql = "CREATE TABLE " + table + " ("
				+ "cardId varchar(18) primary key,"
				+ "CstGUID bigint(20) NOT NULL)";
		Connection con = ADataSource.getConnection(ADataSource.SCHEMA_BIZ);
		Statement stmt = null;
		try {
			stmt = con.createStatement();

			log.info("execute " + sql);
			stmt.execute(sql);

			return true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ADataSource.close(stmt);
			ADataSource.close(con);
		}

		return true;
	} // ~createCardMappingTable

	/**
	 * 创建uid与明源guid的映射表
	 */
	static boolean createGUIDMappingTable(String table) {
		String sql = "CREATE TABLE " + table + " ("
				+ "OriGUID varchar(64) primary key,"
				+ "CstGUID bigint(20) NOT NULL)";
		Connection con = ADataSource.getConnection(ADataSource.SCHEMA_BIZ);
		Statement stmt = null;
		try {
			stmt = con.createStatement();
			log.info("execute " + sql);
			stmt.execute(sql);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ADataSource.close(stmt);
			ADataSource.close(con);
		}

		return true;
	} // ~createGUIDMappingTable

	/**
	 * 创建会员分表
	 */
	static boolean createMemberTable(String table) {
		String sql = "create table if not exists " + table
				+ "(MemGUID bigint(20) not null primary key auto_increment) "
				+ "select * from cst_Member where 1=2";
		Connection con = ADataSource.getConnection(ADataSource.SCHEMA_BIZ);
		Statement stmt = null;
		try {
			stmt = con.createStatement();
			log.info("execute " + sql);
			stmt.execute(sql);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ADataSource.close(stmt);
			ADataSource.close(con);
		}

		return true;
	} // ~createMemberTable

	/**
	 * 创建会员分表
	 */
	static boolean createSaleServiceTable(String table) {
		String sql = "create table if not exists " + table
				+ "(SaleServiceGUID varchar(64) primary key ) "
				+ "select * from s_SaleService where 1=2";
		Connection con = ADataSource.getConnection(ADataSource.SCHEMA_BIZ);
		Statement stmt = null;
		try {
			stmt = con.createStatement();
			log.info("execute " + sql);
			stmt.execute(sql);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ADataSource.close(stmt);
			ADataSource.close(con);
		}

		return true;
	} // ~createMemberTable

	/**
	 * 插入会员sql
	 */
	public static String formatInsertSql(String table, String fields) {
		String sql = "insert into " + table + "(" + fields + ")values(?";

		String[] fs = fields.split(",");
		for (int i = 1; i < fs.length; i++) {
			sql += ",?";
		}

		return sql + ")";
	} // ~formatInsertSql(String table, String fields)

	public static String formatUpdateSql(String table, String fields) {
		String sql = "update " + table + " set ";

		String[] fs = fields.split(",");
		for (int i = 0; i < fs.length; i++) {
			sql += fs[i] + "=?,";
		}

		return sql.substring(0, sql.length() - 1);
	}

	public static CstInfo pickCstInfo(ResultSet rs) throws SQLException {
		CstInfo c = new CstInfo();
		int col = 1;
		c.setOriGUID(rs.getString(col++));
		c.setCstName(rs.getString(col++));
		c.setCardId(rs.getString(col++));

		String officeTel = rs.getString(col++);
		c.setOfficeTel(officeTel);

		String homeTel = rs.getString(col++);
		c.setHomeTel(homeTel);

		String mobile = rs.getString(col++);
		c.setMobile(mobile);

		c.setEmail(rs.getString(col++));
		c.setPostCode(rs.getString(col++));
		c.setAddress(rs.getString(col++));
		c.setCstType(getCstType(rs.getString(col++)));// "CstType,"
		c.setGender(getSex(rs.getString(col++)));
		c.setCardType(getCardType(rs.getString(col++)));// "CardType,"
		c.setFax(rs.getString(col++));
		c.setWorkAddr(rs.getString(col++));
		c.setCountry(rs.getString(col++));
		c.setProvince(rs.getString(col++));
		c.setCity(rs.getString(col++));
		c.setRegitonal(rs.getString(col++));
		c.setRoad(rs.getString(col++));
		rs.getString(col++);// "AddrNO,"
		rs.getString(col++);// "Description,"
		c.setCreateTime(rs.getTimestamp(col++));
		rs.getString(col++); // + "CreatedBy,"
		rs.getString(col++);// + "BizLicence,"
		rs.getString(col++);// + "Corporation,"
		c.setLastModifyTime(rs.getTimestamp(col++));
		rs.getString(col++);// + "ModifyBy,"
		c.setBirthday(rs.getTimestamp(col++));// "BirthDate,"
		c.setCstCode(rs.getString(col++));// + "CstCode,"
		c.setNationality(rs.getString(col++)); // + "Nationality,"
		rs.getString(col++); // + "Preferred,"
		rs.getString(col++); // + "RefuseInfo,"
		rs.getString(col++); // + "FirstContact,"
		rs.getString(col++); // + "CompanyPhone,"
		c.setCreditEvaluate(rs.getString(col++));// + "CreditEvaluate,"
		rs.getString(col++); // + "XltTel,"
		rs.getString(col++); // + "IsWkzy,"
		rs.getString(col++); // + "JzCountry,"
		rs.getString(col++); // + "JzProvince,"
		rs.getString(col++); // + "JzCity,"
		rs.getString(col++); // + "JzRegional,"
		rs.getString(col++); // + "JzRoad,"
		rs.getString(col++); // + "JzAddrNO,"
		rs.getString(col++); // + "DfDate,"
		rs.getString(col++); // + "TjrMemCode,"
		rs.getString(col++); // + "PmemberGUID,"
		rs.getString(col++); // + "ReMark,"
		rs.getString(col++); // + "Age,"
		rs.getString(col++); // "mysoftGUID,
		rs.getString(col++); // CreatedByGUID,
		rs.getString(col++); // IsCreatorUse,
		c.setCardId15(rs.getString(col++)); // CardID15,

		String telList = rs.getString(col++);
		c.setContact(telList); // TelList,

		rs.getString(col++); // CardAddress";

		readLog.info("read:" + c);

		// 如果没有修改时间，以创建时间为修改时间
		if (c.getLastModifyTime() == null) {
			c.setLastModifyTime(c.getCreateTime());
		}

		String contacts = mobile + ";" + homeTel + ";" + officeTel;
		c.getMobiles().addAll(retrieveMobiles(contacts));
		c.setContact(c.getMobiles().toString().replaceAll("\\[", "")
				.replaceAll("\\]", ""));

		c.setMobile(ensureMobile(c.getMobile(), c.getHomeTel(),
				c.getOfficeTel()));
		boolean cValid = CardID.valid(c.getCardId());
		boolean mValid = Util.isMobile(c.getMobile());
		// 定级别
		c.setLevel(4);
		if (cValid) {
			c.setLevel(mValid ? 1 : 2);
		} else {
			c.setLevel(mValid ? 3 : 4);
		}
		//readLog.info("after read:" + c);
		return c;
	} // ~pickCstInfo

	public static int getCstType(String cstType) {
		if (cstType == null) {
			return 0;
		}
		if (cstType.equals("个人")) {
			return 1;
		}
		if (cstType.equals("公司")) {
			return 2;
		}
		return 0;
	}

	public static int getSex(String sex) {
		if (sex == null) {
			return 0;
		}
		sex = sex.trim();
		return "男".equals(sex) ? 1 : ("女".equals(sex) ? 2 : 0);
	}

	public static int getCardType(String type) {
		if (type == null) {
			return 99;
		}
		type = type.trim();
		if (type.trim().equals("")) {
			return 99;
		}
		type = type.trim();
		if ("身份证".equals(type)) {
			return 1;
		}    // 1、
		if ("军官证".equals(type)) {
			return 2;
		}     // 2、
		if ("护照".equals(type)) {
			return 3;
		}     // 3、
		if ("港澳通行证".equals(type)) {
			return 4;
		}  // 4、
		if ("台湾通行证".equals(type)) {
			return 5;
		}  // 5、
		if ("台湾身份证".equals(type)) {
			return 6;
		}  // 6、
		if ("港澳身份证".equals(type)) {
			return 7;
		}  // 7、
		if ("营业执照".equals(type)) {
			return 8;
		}   // 8、
		if ("组织机构码".equals(type)) {
			return 9;
		}  // 9、
		if ("税务登记证".equals(type)) {
			return 10;
		} //10、
		return 99;
	}
	public static String ensureCardid(String cardid, String card15_ignore) {
		cardid = cardid == null ? "" : cardid.trim().toUpperCase();
		if (CardID.valid(cardid)) {
			return cardid;
		}

		//		if (card15 == null) {
		//			return cardid;
		//		}
		//
		//		card15 = card15.trim().toUpperCase();
		//		if (CardID.valid(card15)) {
		//			//			CardID cid = new CardID(card15);
		//			//			String c18 = cid.to18();
		//			//			if (CardID.valid(c18)) {
		//			//				return c18.toUpperCase();
		//			//			}
		//			return card15;
		//		}
		return cardid;
	}

	public static String ensureMobile(String mobile, String homeTel,
			String officeTel) {
		mobile = mobile == null ? "" : mobile.trim();
		if (mobile.length() > 11) {
			mobile = mobile.substring(mobile.length() - 11);
		}
		if (Util.isMobile(mobile)) {
			return mobile;
		}

		homeTel = homeTel == null ? "" : homeTel.trim();
		if (homeTel.length() > 11) {
			homeTel = homeTel.substring(homeTel.length() - 11);
		}
		if (Util.isMobile(homeTel)) {
			return homeTel;
		}

		officeTel = officeTel == null ? "" : officeTel.trim();
		if (officeTel.length() > 11) {
			officeTel = officeTel.substring(officeTel.length() - 11);
		}
		if (Util.isMobile(officeTel)) {
			return officeTel;
		}

		return mobile;
	}

	public static String getSexName(int sex) {
		switch (sex) {
		case 1:
			return "男";
		case 2:
			return "女";
		default:
			return "未知";
		}
	}

	/**
	 * 根据证号取CstGUID
	 */
	public static long getCstGUIDByCardid(Connection con, String cardid) {
		if (!CardID.valid(cardid)) {
			return 0;
		}
		String table = CstUtil.retrieveCardMapping(cardid);
		long guid = 0;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			String sql = "select CstGUID from " + table + " where cardId=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, cardid);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				guid = rs.getLong(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
			log.error("get customer by cardid error: " + cardid);
		} finally {
			ADataSource.close(rs, pstmt, null);
		}
		return guid;
	}

	/**
	 * 根据手机取CstGUID
	 */
	public static long getCstGUIDByMobile(Connection con, String mobile) {
		if (!Util.isMobile(mobile)) {
			return 0;
		}
		String table = CstUtil.retrieveMobileMapping(mobile);
		long guid = 0;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			String sql = "select CstGUID from " + table + " where mobile=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, mobile);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				guid = rs.getLong(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
			log.error("get customer by mobile error: " + mobile);
		} finally {
			ADataSource.close(rs);
			ADataSource.close(pstmt);
		}
		return guid;
	}

	/**
	 * 根据原始GUID取GUID
	 */
	public static long getCstGUIDByOriGuid(Connection con, String oriGuid) {
		String table = CstUtil.retrieveGUIDMapping(oriGuid);
		long guid = 0;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			String sql = "select CstGUID from " + table + " where OriGUID=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, oriGuid);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				guid = rs.getLong(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
			log.error("get customer by origuid error: " + oriGuid);
		}
		ADataSource.close(rs);
		ADataSource.close(pstmt);
		return guid;
	} //~

	public static CstInfo getByGuid(Connection con, long uid) {
		String table = CstUtil.retrieveCustomerTable(uid);
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			CstInfo c = null;
			String fields = "CstGUID,"
					+ "CstName,"
					+ "Gender,"
					+ "BirthDate,"
					+ "Language,"
					+ "CstType,"
					+ "CardType,"
					+ "CardID,"
					+ "MobileTel,"
					+ "OfficeTel,"
					+ "HomeTel,"
					+ "Fax,"
					+ "Email,"
					+ "PostCode,"
					+ "Address,"
					+ "Country,"
					+ "Province,"
					+ "City,"
					+ "Regional,"
					+ "Road,"
					+ "Nationality,"
					+ "NativePlace,"
					+ "SrcType,"
					+ "LastModifyTime,"
					+ "OriGUID,"
					+ "CreditEvaluate,"
					+ "CstCode,"
					+ "Company,"
					+ "Position,"
					+ "Incoming,"
					+ "WorkAddr,"
					+ "CompanyTel,"
					+ "Race,"
					+ "Religion,"
					+ "Political,IsMerried,cogniChannel,status,CreateTime,CardID15,Level,`contact`";

			String sql = "select " + fields + " from " + table
					+ " where CstGUID=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1, uid);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				c = new CstInfo();
				c.setCstGUID(uid);
				c.setCstName(rs.getString("CstName"));
				c.setGender(rs.getInt("Gender"));
				c.setBirthday(rs.getTimestamp("BirthDate"));
				c.setLanguage(rs.getString("Language"));
				c.setCstType(rs.getInt("CstType"));
				c.setCardType(rs.getInt("CardType"));
				c.setCardId(rs.getString("CardID"));
				c.setMobile(rs.getString("MobileTel"));
				c.setOfficeTel(rs.getString("OfficeTel"));
				c.setHomeTel(rs.getString("HomeTel"));
				c.setFax(rs.getString("Fax"));
				c.setEmail(rs.getString("Email"));
				c.setPostCode(rs.getString("PostCode"));
				c.setAddress(rs.getString("Address"));
				c.setCountry(rs.getString("Country"));
				c.setProvince(rs.getString("Province"));
				c.setCity(rs.getString("City"));
				c.setRegitonal(rs.getString("Regional"));
				c.setRoad(rs.getString("Road"));
				c.setNationality(rs.getString("Nationality"));
				c.setNativePlace(rs.getString("NativePlace"));
				c.setSrcType(rs.getString("SrcType"));
				c.setLastModifyTime(rs.getTimestamp("LastModifyTime"));
				c.setOriGUID(rs.getString("OriGUID"));
				c.setCreditEvaluate(rs.getString("CreditEvaluate"));
				c.setCstCode(rs.getString("CstCode"));
				c.setCompany(rs.getString("Company"));
				c.setPosition(rs.getString("Position"));
				c.setIncoming(rs.getFloat("Incoming"));
				c.setWorkAddr(rs.getString("WorkAddr"));
				c.setCompanyTel(rs.getString("CompanyTel"));
				c.setRace(rs.getString("Race"));
				c.setReligion(rs.getString("Religion"));
				c.setPolitical(rs.getString("Political"));
				c.setIsMerried(rs.getInt("IsMerried"));
				c.setCogniChannel(rs.getString("cogniChannel"));
				c.setStatus(rs.getInt("status"));
				c.setCreateTime(rs.getTimestamp("CreateTime"));
				c.setCardId15(rs.getString("CardID15"));
				c.setLevel(rs.getInt("Level"));
				c.setContact(rs.getString("contact"));
			}
			return c;
		} catch (Exception e) {
			e.printStackTrace();
			log.error("get customer by guid error: " + uid);
			return null;
		} finally {
			ADataSource.close(rs, pstmt, null);
		}
	}

	// GUID找不到对应的CstGUID，表明原本关系被洗掉了，按GUID再找一次
	public static long retrieveMemship(String oriCstGUID) {
		Connection con = ADataSource.getConnection(ADataSource.SCHEMA_MY);
		Connection bizCon = ADataSource.getConnection(ADataSource.SCHEMA_BIZ);
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		long uid = 0;

		try {
			// 从明源库，根据GUID查身份证和手机
			String tbl = "p_Customer";
			String sql = "select CardID from " + tbl + " where CstGUID=?";
			pstmt = con.prepareStatement(sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			pstmt.setString(1, oriCstGUID);

			rs = pstmt.executeQuery();
			String card = null;
			//			String card15 = null;
			//			String mobile = null, homeTel, officeTel;
			if (rs.next()) {
				card = rs.getString(1);
				//				mobile = rs.getString(2);
				//				card15 = rs.getString(3);
				//				homeTel = rs.getString(4);
				//				officeTel = rs.getString(5);
			} else {
				log.error("no cardid for " + oriCstGUID);
				return new Long(0);
			}

			// 首先按身份证查找
			card = card == null ? "" : card.toUpperCase().trim();
			uid = getCstGUIDByCardid(bizCon, card);
			return uid;
			//			if (uid != 0) {
			//				return uid;
			//			}
			// 如果没查到，按15位身份证查
			//			card = ensureCardid(card, card15);
			//			uid = getCstGUIDByCardid(bizCon, card);
			//			if (uid != 0) {
			//				return uid;
			//			}

			// 仍未查到，则按手机查
			//			mobile = mobile == null ? "" : mobile.trim();
			//			if (mobile.length() > 11) {
			//				mobile = mobile.substring(mobile.length() - 11);
			//			}
			//			try {
			//				// 根据手机映射查
			//				uid = getCstGUIDByMobile(bizCon, mobile);
			//			} catch (Exception e) {
			//				log.error("get cstguid by mobile " + mobile + "error:" + e);
			//			}

			// 仍未查到，则以家庭电话，办公电话中有效的手机查
			//			mobile = ensureMobile(mobile, homeTel, officeTel);
			//			try {
			//				// 根据手机映射查
			//				uid = getCstGUIDByMobile(bizCon, mobile);
			//			} catch (Exception e) {
			//				log.error("get cstguid by mobile " + mobile + "error:" + e);
			//			}
			//
			//			// 返回查询结果
			//			if (uid != 0) {
			//				return uid;
			//			} else {
			//				log.error("no cardid[" + card + "] and mobile[" + mobile
			//						+ "] mapping in datecenter");
			//				return new Long(0);
			//			}
		} catch (Exception e) {
			log.error("retrieve memship for " + oriCstGUID + " error: " + e);
		} finally {
			ADataSource.close(rs);
			ADataSource.close(pstmt);
			ADataSource.close(con);
			ADataSource.close(bizCon);
		}
		return new Long(0);
	}  //~retrieveMemship(String oriCstGUID)

	//	private static Pattern p = Pattern.compile("[1]\\d{10}");
	private static Pattern mp = Pattern
			.compile("((13[0-9])|(14[0-9])|(15[0-9])|(17[0-9])|(18[0-9]))\\d{8}");

	public static Collection<String> retrieveMobiles(String str) {
		if (str == null) {
			return new HashSet<String>();
		}
		str = str.trim().replaceAll("-", "");
		Set<String> set = new HashSet<String>();
		try {
			StringBuilder sb = new StringBuilder("");
			for (int i = 0; i < str.length(); i++) {
				char ch = str.charAt(i);
				sb.append(ch == '\\' ? ";" : ch);
			}
			String[] mobiles = sb.toString().split("[\\s;,；，、/]");
			for (String mobile : mobiles) {
				mobile = mobile.trim();
				if (mobile.length() < 11) {
					continue;
				}
				Matcher m = mp.matcher(mobile);
				while (m.find()) {
					String group = m.group();
					if (!Util.isMobile(group)) {
						continue;
					}
					set.add(group);
				}
			} // end for
		} catch (Exception e) {
			e.printStackTrace();
		}
		return set;
	} //~retrieveMobiles(String str)

	/**
	 * 发送客户合并通知
	 */
	public static void sendMergeNotice(long retain, long droped, Date mergeTime) {
		try {
			JSONObject json = new JSONObject();
			// cstRetain 是 int 合并时保留继续使用的客户ID
			// cstDropped 是 int 合并时丢弃不再使用的客户ID
			// mergeTime 是 int 合并时间
			if (mergeTime == null) {
				mergeTime = new Date();
			}
			json.put("cstRetain", retain);
			json.put("cstDropped", droped);
			json.put("mergeTime", mergeTime.getTime() / 1000);

			QueueMapMessage ms = new QueueMapMessage();
			ms.setMessageType(C.NOTICE_CST_MERGE);
			ms.setObj(json.toString());
			ms.send();
		} catch (Exception e) {
			log.error("send cst notice error:" + Util.formatStackTrace(e));
		}
	} //~sendMergeNotice
} // /~
